﻿using System;
using System.Data;
using System.Data.OleDb;
using Fitecom.DataSet;

namespace Fitecom
{
    public class AccessDatabase
    {
        private static string GetSubstring(string value, int lenght)
        {
            if (string.IsNullOrEmpty(value))
                return "";
            if (value.Length <= lenght)
                return value;
            return value.Substring(0, lenght);
        }

        private static bool ExistsClient(OleDbTransaction oleDbTransaction, string cliente)
        {
            const string query =
                "SELECT count(*) " +
                "FROM   CLIENTES " +
                "WHERE  [cliente] = @cliente";
            int result = 0;

            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@cliente", cliente);
                using (OleDbDataReader reader = oleDbCommand.ExecuteReader())
                {
                    while (reader.Read())
                        result = reader.GetInt32(0);
                }

            }
            return result > 0;
        }

        private static void InsertClient(OleDbTransaction oleDbTransaction, string cliente, string nome, string pais, string vendedor)
        {
            const string query =
                "INSERT INTO CLIENTES " +
                "([cliente], [nome], [pais], [vendedor], [dtcria], [dtmod], [utiliz]) " +
                "Values (?, ?, ?, ?, ?, ?, 'Primavera')";
            DateTime dateTime = DateTime.Today;
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@cliente", cliente);
                oleDbCommand.Parameters.AddWithValue("@nome", nome);
                oleDbCommand.Parameters.AddWithValue("@pais", pais == "" ? Convert.DBNull : pais);
                oleDbCommand.Parameters.AddWithValue("@vendedor", vendedor == "" ? Convert.DBNull : vendedor);
                oleDbCommand.Parameters.AddWithValue("@dtcria", dateTime);
                oleDbCommand.Parameters.AddWithValue("@dtmod", dateTime);                
                oleDbCommand.ExecuteNonQuery();
            }
        }

        private static void UpdateClient(OleDbTransaction oleDbTransaction, string cliente, string nome, string pais, string vendedor)
        {
            const string query =
                "UPDATE CLIENTES " +
                "SET    [nome] = ?," +
                "       [pais] = ?," +
                "       [vendedor] = ?," +
                "       [dtmod] = ?" +
                "WHERE  [cliente] = ?";
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@nome", nome);
                oleDbCommand.Parameters.AddWithValue("@pais", pais == "" ? Convert.DBNull : pais);
                oleDbCommand.Parameters.AddWithValue("@vendedor", vendedor == "" ? Convert.DBNull : vendedor);
                oleDbCommand.Parameters.AddWithValue("@dtmod", DateTime.Today);
                oleDbCommand.Parameters.AddWithValue("@cliente", cliente);
                oleDbCommand.ExecuteNonQuery();
            }
        }

        public static void SaveClient(OleDbTransaction oleDbTransaction, string cliente, string nome, string pais, string vendedor)
        {
            if (ExistsClient(oleDbTransaction, cliente))
                UpdateClient(oleDbTransaction, cliente, nome, pais, vendedor);
            else
                InsertClient(oleDbTransaction, cliente, nome, pais, vendedor);
        }

        public static void DeleteClient(OleDbTransaction oleDbTransaction, string cliente)
        {
            const string query =
                "DELETE FROM CLIENTES " +
                "WHERE  [cliente] = ?";
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@cliente", cliente);
                oleDbCommand.ExecuteNonQuery();
            }
        }


        private static bool ExistsSupplier(OleDbTransaction oleDbTransaction, long numfor)
        {
            const string query =
                "SELECT count(*) " +
                "FROM   fornecedor " +
                "WHERE  [numfor] = @numfor";
            int result = 0;
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@numfor", numfor);
                using (OleDbDataReader reader = oleDbCommand.ExecuteReader())
                {
                    while (reader.Read())
                        result = reader.GetInt32(0);
                }

            }
            return result > 0;
        }

        private static void InsertSupplier(OleDbTransaction oleDbTransaction, long numfor, string nome, string pais)
        {
            const string query =
                "INSERT INTO FORNECEDOR " +
                "([numfor], [nome], [pais], [dtcria], [dtmod], [utiliz]) " +
                "Values (?, ?, ?, ?, ?, 'Primavera') ";
            DateTime now = DateTime.Today;
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@numfor", numfor);
                oleDbCommand.Parameters.AddWithValue("@nome", GetSubstring(nome, 50));
                oleDbCommand.Parameters.AddWithValue("@pais", GetSubstring(pais, 2) == "" ? Convert.DBNull : GetSubstring(pais, 2));
                oleDbCommand.Parameters.AddWithValue("@dtcria", now);
                oleDbCommand.Parameters.AddWithValue("@dtmod", now);
                oleDbCommand.ExecuteNonQuery();
            }
        }

        private static void UpdateSupplier(OleDbTransaction oleDbTransaction, long numfor, string nome, string pais)
        {
            const string query =
                "UPDATE FORNECEDOR " +
                "SET    [nome] = @nome, " +
                "       [pais] = @pais, " +
                "       [dtmod] = @dtmod " +
                "WHERE  [numfor] = @numfor";
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@nome", GetSubstring(nome, 50));
                oleDbCommand.Parameters.AddWithValue("@pais", GetSubstring(pais, 2) == "" ? Convert.DBNull : GetSubstring(pais, 2));
                oleDbCommand.Parameters.AddWithValue("@dtmod", DateTime.Today);
                oleDbCommand.Parameters.AddWithValue("@numfor", numfor);
                oleDbCommand.ExecuteNonQuery();
            }
        }

        public static void SaveSupplier(OleDbTransaction oleDbTransaction, long numfor, string nome, string pais)
        {
            if (ExistsSupplier(oleDbTransaction, numfor))
                UpdateSupplier(oleDbTransaction, numfor, nome, pais);
            else
                InsertSupplier(oleDbTransaction, numfor, nome, pais);
        }

        public static void DeleteSupplier(OleDbTransaction oleDbTransaction, string numfor)
        {
            const string query =
                "DELETE FROM fornecedor " +
                "WHERE  [numfor] = ?";
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@numfor", numfor);
                oleDbCommand.ExecuteNonQuery();
            }
        }


        private static bool ExistsArticle(OleDbTransaction oleDbTransaction, string artigo)
        {
            const string query =
                "SELECT count(*) " +
                "FROM   PRODACAB " +
                "WHERE  [artigo] = @artigo";
            int result = 0;
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@artigo", artigo);
                using (OleDbDataReader reader = oleDbCommand.ExecuteReader())
                {
                    while (reader.Read())
                        result = reader.GetInt32(0);
                }

            }
            return result > 0;
        }

        private static void InsertArticle(OleDbTransaction oleDbTransaction, string artigo, string descricao, string familia, string subfamilia, double pvp, string composicao, double peso,
            double comprimento, double largura, string acabamento, string padrao, string cor, string unidadeBase)
        {
            const string query =
                "INSERT INTO PRODACAB " +
                "([artigo], [descricao], [familia], [subfamilia], [pvp], [composicao], [peso], [comprimento], [largura], [acabamento], [padrao], [cor], [unidade], [dtcria], [dtmod], [utiliz])" +
                "Values (@artigo, @descricao, @familia, @subfamilia, @pvp, @composicao, @peso, @comprimento, @largura, @acabamento, @padrao, @cor, @unidadeBase, @dtcria, @dtmod, 'Primavera')";
            DateTime dateTime = DateTime.Today;
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@artigo", artigo);
                oleDbCommand.Parameters.AddWithValue("@descricao", descricao == "" ? Convert.DBNull : descricao);
                oleDbCommand.Parameters.AddWithValue("@familia", familia == "" ? Convert.DBNull : familia);
                oleDbCommand.Parameters.AddWithValue("@subfamilia", subfamilia == "" ? Convert.DBNull : subfamilia);
                oleDbCommand.Parameters.AddWithValue("@pvp", Math.Round(pvp, 2));
                oleDbCommand.Parameters.AddWithValue("@composicao", composicao == "" ? Convert.DBNull : composicao);
                oleDbCommand.Parameters.AddWithValue("@peso", Math.Round(peso, 2));
                oleDbCommand.Parameters.AddWithValue("@comprimento", Math.Round(comprimento, 2));
                oleDbCommand.Parameters.AddWithValue("@largura", Math.Round(largura, 2));
                oleDbCommand.Parameters.AddWithValue("@acabamento", acabamento);
                oleDbCommand.Parameters.AddWithValue("@padrao", padrao);
                oleDbCommand.Parameters.AddWithValue("@cor", cor);
                oleDbCommand.Parameters.AddWithValue("@unidadeBase", unidadeBase == "" ? Convert.DBNull : unidadeBase);
                oleDbCommand.Parameters.AddWithValue("@dtcria", dateTime);
                oleDbCommand.Parameters.AddWithValue("@dtmod", dateTime);                
                oleDbCommand.ExecuteNonQuery();
            }
        }

        private static void UpdateArticle(OleDbTransaction oleDbTransaction, string artigo, string descricao, string familia, string subfamilia, double pvp, string composicao, double peso,
            double comprimento, double largura, string acabamento, string padrao, string cor, string unidadeBase)
        {
            const string query =
                "UPDATE PRODACAB " +
                "SET    [descricao] = ?," +
                "       [familia] = ?," +
                "       [subfamilia] = ?," +
                "       [pvp] = ?," +
                "       [composicao] = ?," +
                "       [peso] = ?," +
                "       [comprimento] = ?," +
                "       [largura] = ?," +
                "       [acabamento] = ?, " +
                "       [padrao] = ?, " +
                "       [cor] = ?, " +
                "       [unidade] = ?, " +
                "       [dtmod] = ?" +
                "WHERE  [artigo] = ?";
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@descricao", descricao == "" ? Convert.DBNull : descricao);
                oleDbCommand.Parameters.AddWithValue("@familia", familia == "" ? Convert.DBNull : familia);
                oleDbCommand.Parameters.AddWithValue("@subfamilia", subfamilia == "" ? Convert.DBNull : subfamilia);
                oleDbCommand.Parameters.AddWithValue("@pvp", Math.Round(pvp,2));
                oleDbCommand.Parameters.AddWithValue("@composicao", composicao == "" ? Convert.DBNull : composicao);
                oleDbCommand.Parameters.AddWithValue("@peso", Math.Round(peso,2));
                oleDbCommand.Parameters.AddWithValue("@comprimento", Math.Round(comprimento,2));
                oleDbCommand.Parameters.AddWithValue("@largura", Math.Round(largura,2));
                oleDbCommand.Parameters.AddWithValue("@acabamento", acabamento);
                oleDbCommand.Parameters.AddWithValue("@padrao", padrao);
                oleDbCommand.Parameters.AddWithValue("@cor", cor);
                oleDbCommand.Parameters.AddWithValue("@unidadeBase", unidadeBase == "" ? Convert.DBNull : unidadeBase);
                oleDbCommand.Parameters.AddWithValue("@dtmod", DateTime.Today);
                oleDbCommand.Parameters.AddWithValue("@artigo", artigo);
                oleDbCommand.ExecuteNonQuery();
            }
        }

        public static void SaveArticle(OleDbTransaction oleDbTransaction, string artigo, string descricao, string familia, string subfamilia, double pvp, string composicao, double peso,
            double comprimento, double largura, string acabamento, string padrao, string cor, string unidadeBase)
        {
            if (ExistsArticle(oleDbTransaction, artigo))
                UpdateArticle(oleDbTransaction, artigo, descricao, familia, subfamilia, pvp, composicao, peso, comprimento, largura, acabamento, padrao, cor, unidadeBase);
            else
                InsertArticle(oleDbTransaction, artigo, descricao, familia, subfamilia, pvp, composicao, peso, comprimento, largura, acabamento, padrao, cor, unidadeBase);
        }

        public static void DeleteArticle(OleDbTransaction oleDbTransaction, string artigo)
        {
            const string query =
                "DELETE FROM PRODACAB " +
                "WHERE  [artigo] = ?";
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@artigo", artigo);
                oleDbCommand.ExecuteNonQuery();
            }
        }


        private static bool ExistsCompound(OleDbTransaction oleDbTransaction, string composicao, string familia)
        {
            const string query =
                "SELECT count(*) " +
                "FROM   Composicoes " +
                "WHERE  [composicao] = @composicao"+
                "       and [familia] = @familia";
            int result = 0;
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@composicao", composicao);
                oleDbCommand.Parameters.AddWithValue("@familia", familia);
                using (OleDbDataReader reader = oleDbCommand.ExecuteReader())
                {
                    while (reader.Read())
                        result = reader.GetInt32(0);
                }
            }
            return result > 0;
        }

        private static void InsertCompound(OleDbTransaction oleDbTransaction, string composicao, string familia, string descricao)
        {
            const string query =
                "INSERT INTO COMPOSICOES " +
                "([composicao], [familia], [descricao], [dtcria], [dtmod], [utiliz])" +
                "Values (@composicao, @familia, @descricao, @dtcria, @dtmod, 'Primavera')";
            DateTime dateTime = DateTime.Today;
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@composicao", composicao);
                oleDbCommand.Parameters.AddWithValue("@familia", familia);
                oleDbCommand.Parameters.AddWithValue("@descricao", descricao == "" ? Convert.DBNull : descricao);
                oleDbCommand.Parameters.AddWithValue("@dtcria", dateTime);
                oleDbCommand.Parameters.AddWithValue("@dtmod", dateTime);
                oleDbCommand.ExecuteNonQuery();
            }
        }

        private static void UpdateCompound(OleDbTransaction oleDbTransaction, string composicao, string familia, string descricao)
        {
            const string query =
                "UPDATE COMPOSICOES " +
                "SET    [descricao] = ?," +
                "       [dtmod] = ? " +
                "WHERE  [composicao] = ?" +
                "       and [familia] = ?";
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@descricao", descricao == "" ? Convert.DBNull : descricao);
                oleDbCommand.Parameters.AddWithValue("@dtmod", DateTime.Today);
                oleDbCommand.Parameters.AddWithValue("@composicao", composicao);
                oleDbCommand.Parameters.AddWithValue("@familia", familia);                
                oleDbCommand.ExecuteNonQuery();
            }
        }

        public static void SaveCompound(OleDbTransaction oleDbTransaction, string composicao, string familia, string descricao)
        {
            if (ExistsCompound(oleDbTransaction, composicao, familia))
                UpdateCompound(oleDbTransaction, composicao, familia, descricao);
            else
                InsertCompound(oleDbTransaction, composicao, familia, descricao);
        }

        public static ComposicoesDataSet GetCompounds(OleDbTransaction oleDbTransaction)
        {
            const string query =
                "SELECT composicao, familia " +
                "FROM   Composicoes ";
            var dataSet = new ComposicoesDataSet();
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                using (OleDbDataReader reader = oleDbCommand.ExecuteReader())
                {
                    while (reader.Read())
                        dataSet.Composicao.AddComposicaoRow(reader.GetString(0), reader.GetString(1));
                }
            }
            return dataSet;
        }

        public static void DeleteCompound(OleDbTransaction oleDbTransaction, string composicao, string familia)
        {
            const string query =
                "DELETE FROM COMPOSICOES " +
                "WHERE  [composicao] = ?" +
                "       and [familia] = ?";
            using (var oleDbCommand = new OleDbCommand(query, oleDbTransaction.Connection, oleDbTransaction))
            {
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddWithValue("@composicao", composicao);
                oleDbCommand.Parameters.AddWithValue("@familia", familia);
                oleDbCommand.ExecuteNonQuery();
            }
        }


//Saved Queries
//        string ConnString = Utils.GetConnString();
//string SqlString = "AddContact";
//using (OleDbConnection conn = new OleDbConnection(ConnString))
//{
//  using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
//  {
//    cmd.CommandType = CommandType.StoredProcedure;
//    cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
//    cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
//    conn.Open();
//    cmd.ExecuteNonQuery();
//  }
//}





    }
}
